#coding=utf-8
from stockdb import *
from stseries.basic import *
import datetime
from sqlalchemy.exceptions import InvalidRequestError

today = datetime.date.today().isoformat()
szzs = sess.query(SIndex).filter_by(name='上证指数').one()

def get_stcodes():
    '''获取数据库中的全部股票的代码集'''
    ss = sess.query(Stock).order_by(tables.stock.c.code).all()
    cs = [s.code for s in ss]
    return cs
    
def find_stock(code):
    #print code
    return sess.query(Stock).filter_by(code=code).one()
    
def find_tds_on(date):
    return sess.query(TradeDay).join('fins').filter_by(type='stock').\
           reset_joinpoint().filter_by(date=date).all()
               
def clear_rpcache():
    sess.connection().execute('delete from rpcache')

def create_szzs_tdstats(date):
    
    std = szzs.find_td(date)
    if std.stats:
        print 'stats created;'
        return
    
    tds = find_tds_on(date)
    chs = [td.price.change for td in tds if td.price.change]
    #print len(chs)

    tsts = TDStats()
    tsts.td = szzs.find_td(date)
    tsts.num_of_trading = len(chs)
    
    tsts.num_of_up = len( filter(lambda x:x > 0, chs) )
    tsts.num_of_down = len( filter(lambda x:x < 0, chs) )
    tsts.num_of_still = len( filter(lambda x:x  == 0, chs) )
    
    tsts.average_change = average(chs)
    tsts.median_change = median(chs)
    
    sess.save(tsts)
    sess.commit()
    
    return tsts

def get_or_create_ts(code, date, quantity):

    s = find_stock(code)
    
    try:
        return sess.query(TradableShare).\
                    filter_by(stock=s, date=date, quantity=quantity).one()
                    
    except InvalidRequestError:
        
        ts = TradableShare()
        ts.stock = s
        ts.date = date
        ts.quantity = quantity
        
        print 'create ts(%s, %s, %s, %s)'%(code, s.name, date, quantity)
        
        sess.save(ts)
        sess.commit()
        
def get_or_create_fqr(code, date, ratio):

    s = find_stock(code)
    
    td = s.find_td(date)
    
    if not td:return
    
    if td.price.fqr == ratio:return
    
    f = FuQuanRatio()
    f.stock = s
    f.date = date
    f.ratio = ratio
    
    sess.save(f)
    sess.commit()
    
    print 'create fuquan_ratio(%s, %s, %s, %s)'%(code, s.name, date, ratio)
                
def pprint_tds(tds, need_reverse=True):
    if need_reverse:
        tds.reverse()
    
    print '日期', '收盘价', '最低价', '最高价', '涨幅', '波动幅度','换手率', '大单b-s(万元)','最大笔买盘(万元)', '最大笔卖盘(万元)'
    
    for td in tds:
        p = td.price
        print td.date, td.price.close, '%.2f'%p.lowest,'%.2f'%p.highest ,'%.2f%%'%p.change, '%.2f'%p.fluctuation,'%.2f%%'%p.trade_rate ,int(td.b_s()/10000), int(td.max_bmoney/10000), int(td.get_maxmoney('s')/10000)

def calculate_szzs_traderate(dt):
    
    x = 0L;y = 0L
    
    tds = find_tds_on(dt)
    if not len(tds):return None
    
    for td in tds:
        y = y + td.fins.ts
        x = x + td.price.volume
    
    return x * 100.0 / y
    
